package com.cis467.mrball.backend;

import java.util.ArrayList;

import com.cis467.mrball.parts.LevelWrapper;
import com.cis467.mrball.parts.TestLevelWrapper;

import android.app.Application;
import android.app.Service;
import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.IBinder;


// TODO This should probably be an abstract class that extends service.
// TODO Then is can be extended once for the local service, and separately for the remote service.

// TODO Create a thread within this to make calls
public class MrBallSQLiteDBManager extends Service{ 

	private Application mApplication;  // Figure out why this is needed
	
	private MrBallSQLiteOpenHelper mSQLiteOpenHelper;
	
	private SQLiteDatabase mMrBallDB;
	
	
	@Override
	public IBinder onBind(Intent intent) {
		return null;
	}
	
	@Override
	public void onCreate() {
		
		super.onCreate();
		
		// Get the application that owns this service
		mApplication = getApplication();
		
		// Get a DB Open Helper
		mSQLiteOpenHelper = new MrBallSQLiteOpenHelper(this);
		
		// Open the DB
		mMrBallDB = mSQLiteOpenHelper.getWritableDatabase();
	}
	
	
	
	// Used for saving levels
	public void insertLevel(LevelWrapper levelWrapper) {
		
		String table = DatabaseConstants.TABLE_LEVELS;
		
		ContentValues values = new ContentValues();
		values.put(DatabaseConstants.FIELD_CREATOR, levelWrapper.mCreator);
		values.put(DatabaseConstants.FIELD_LEVEL_NAME, levelWrapper.mLevelName);
		values.put(DatabaseConstants.FIELD_LEVEL_BATCH, levelWrapper.mLevelBatch);
		values.put(DatabaseConstants.FIELD_LEVEL_NUMBER, levelWrapper.mLevelNumber);
		values.put(DatabaseConstants.FIELD_LEVEL_XML, levelWrapper.mLevel.toString());
		values.put(DatabaseConstants.FIELD_PERSONAL_BEST, levelWrapper.mPersonalBest);
		values.put(DatabaseConstants.FIELD_WORLD_RECORD, levelWrapper.mWorldRecord);
		values.put(DatabaseConstants.FIELD_LOCAL_VOTE, levelWrapper.mLocalVote);
		values.put(DatabaseConstants.FIELD_SERVER_VOTE, 0);
		values.put(DatabaseConstants.FIELD_UPVOTES, 0);
		values.put(DatabaseConstants.FIELD_DOWNVOTES, 0);
		
		try {
			mMrBallDB.insert(table, null, values);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void deleteLevel(String creator, String levelName, String batchName) {
		
		String table = 		 DatabaseConstants.TABLE_LEVELS;
		String whereClause = DatabaseConstants.FIELD_CREATOR 		+ " =? AND " +
							 DatabaseConstants.FIELD_LEVEL_NAME 	+ " =? AND " +
							 DatabaseConstants.FIELD_LEVEL_BATCH 	+ " =?";
		String[] whereArgs = {creator, levelName, batchName};
		
		try {
			mMrBallDB.delete(table, whereClause, whereArgs);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void insertCoreLevels() {		
		// TODO make a raw resource and read the levels from it
		
		// TODO remove this stuff
		// Create and insert a test level (as a core level)
		LevelWrapper test = new TestLevelWrapper().getWrapper();
		test.mLevelBatch = DatabaseConstants.BATCH_CORE;
		insertLevel(test);
	}
	
	public void insertBuilt(LevelWrapper levelWrapper) {
		
		// Delete the old level
		deleteLevel(levelWrapper.mCreator, levelWrapper.mLevelName, DatabaseConstants.BATCH_BUILT);
		
		// Make sure the new level is marked as build
		levelWrapper.mLevelBatch = DatabaseConstants.BATCH_BUILT;
		
		// Insert new level
		insertLevel(levelWrapper);
	}
	
	// TODO make it so that already downloaded levels are not shown as downloadable
	public void insertDownloaded(LevelWrapper levelWrapper) {
		
		// Make sure the new level is marked as downloaded
		levelWrapper.mLevelBatch = DatabaseConstants.BATCH_DOWNLOADED;
		
		// Insert new level (constraint will cause this to be ignored if level already downloaded)
		insertLevel(levelWrapper);
	}
	
	public void updateLocalHighScore(LevelWrapper levelWrapper) {
		
		String table = DatabaseConstants.TABLE_LEVELS;
		
		ContentValues values = new ContentValues();
		values.put(DatabaseConstants.FIELD_PERSONAL_BEST, levelWrapper.mPersonalBest);
		
		String whereClause = DatabaseConstants.FIELD_CREATOR 		+ " =? AND " +
				 			 DatabaseConstants.FIELD_LEVEL_NAME 	+ " =? AND " +
				 			 DatabaseConstants.FIELD_LEVEL_BATCH 	+ " =?";
		String[] whereArgs = {levelWrapper.mCreator, levelWrapper.mLevelName, levelWrapper.mLevelBatch};
		
		try {
			mMrBallDB.update(table, values, whereClause, whereArgs);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void updateWorldData(LevelWrapper levelWrapper) {
		
		// TODO what needs to be updated? world record, upvotes, downvotes?
	}
	
	// TODO Refactor this algorithm. There has to be a better way. 
	private ArrayList<LevelWrapper> convertToArrayList(Cursor results) {
		
		ArrayList<LevelWrapper> levels = new ArrayList<LevelWrapper>();
		
		int numColumns = results.getColumnCount();

		results.moveToFirst();
		
		while(!results.isAfterLast()) {
		
			LevelWrapper level = new LevelWrapper();
			
			for (int i = 0; i < numColumns; i++) {
				
				String columnName = results.getColumnName(i);
				
				if(columnName.equals(DatabaseConstants.FIELD_CREATOR)) {
					level.mCreator = results.getString(i);
				}
				else if(columnName.equals(DatabaseConstants.FIELD_LEVEL_NAME)) {
					level.mLevelName = results.getString(i);
				}
				else if(columnName.equals(DatabaseConstants.FIELD_LEVEL_BATCH)) {
					level.mLevelBatch = results.getString(i);
				}
				else if(columnName.equals(DatabaseConstants.FIELD_LEVEL_NUMBER)) {
					level.mLevelNumber = results.getInt(i);
				}
				else if(columnName.equals(DatabaseConstants.FIELD_LEVEL_XML)) {
					level.mLevel = LocalContent.convertXML(results.getString(i));
				}
				else if(columnName.equals(DatabaseConstants.FIELD_PERSONAL_BEST)) {
					level.mPersonalBest = results.getInt(i);
				}
				else if(columnName.equals(DatabaseConstants.FIELD_WORLD_RECORD)) {
					level.mWorldRecord = results.getInt(i);
				}
				else if(columnName.equals(DatabaseConstants.FIELD_LOCAL_VOTE)) {
					level.mLocalVote = results.getInt(i);
				}
			}
			
			levels.add(level);
			
			results.moveToNext();
		}
		
		results.close();
		
		return levels;
	}
	
	public ArrayList<LevelWrapper> selectCoreLevels() {
		
		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_LEVEL_BATCH + " =?";
		
		String[] whereArgs = {DatabaseConstants.BATCH_CORE};
		
		String orderBy = DatabaseConstants.FIELD_LEVEL_NUMBER + " ASCENDING";
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, orderBy);
		
		return convertToArrayList(results);
	}
	
	public ArrayList<LevelWrapper> selectBuiltLevels() {

		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_LEVEL_BATCH + " =?";
		
		String[] whereArgs = {DatabaseConstants.BATCH_BUILT};
		
		String orderBy = DatabaseConstants.FIELD_LEVEL_NAME + " ASCENDING";
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, orderBy);
		
		return convertToArrayList(results);
	}
	
	public ArrayList<LevelWrapper> selectDownloadedLevels() {

		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_LEVEL_BATCH + " =?";
		
		String[] whereArgs = {DatabaseConstants.BATCH_DOWNLOADED};
		
		String orderBy = DatabaseConstants.FIELD_UPVOTES + " ASCENDING";
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, orderBy);
		
		return convertToArrayList(results);
	}
	
	public LevelWrapper selectLevel(String creator, String levelName) {

		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_CREATOR + " =? " +
					   DatabaseConstants.FIELD_LEVEL_NAME + " =?";
		
		String[] whereArgs = {creator, levelName};
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, null);
		
		return convertToArrayList(results).get(0);
	}
	
	public LevelWrapper selectBatchLevel(String creator, String batchName, int levelNumber) {

		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_CREATOR + " =? " +
					   DatabaseConstants.FIELD_LEVEL_BATCH + " =? " +
					   DatabaseConstants.FIELD_LEVEL_NUMBER + " =?";
		
		String[] whereArgs = {creator, batchName, ("" + levelNumber)};
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, null);
		
		return convertToArrayList(results).get(0);
	}
	
	
	public long selectLocalHighScore(String creator, String levelName) {

		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_CREATOR + " =? " +
					   DatabaseConstants.FIELD_LEVEL_NAME + " =?";
		
		String[] whereArgs = {creator, levelName};
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, null);
		
		return convertToArrayList(results).get(0).mPersonalBest;
	}
	
	public ArrayList<LevelWrapper> selectChangedLocalVotes() {
		
		// Select all levels where localvote != servervote
		// Built arraylist (perhaps with empty level object)

		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_LEVEL_BATCH + " =? " +
					   DatabaseConstants.FIELD_LOCAL_VOTE + " !=? ";
		
		String[] whereArgs = {DatabaseConstants.BATCH_BUILT, DatabaseConstants.FIELD_SERVER_VOTE};
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, null);
		
		return convertToArrayList(results);
	}
	
	// For telling the server which levels we have. It will respond with update for each.
	public ArrayList<LevelWrapper> selectDownloadedList() {
		
		String table = DatabaseConstants.TABLE_LEVELS;
		
		String where = DatabaseConstants.FIELD_LEVEL_BATCH + " =? ";
		
		String[] whereArgs = {DatabaseConstants.BATCH_DOWNLOADED};
				
		Cursor results = mMrBallDB.query(table, null, where, whereArgs, null, null, null);
		
		return convertToArrayList(results);
	}
	
	




	
	
	
	
}
